﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    using DAL.Entities;

    public class NhanvienDAL : BaseDAL, UTL.IBaseDAL 
    {
        public int Count()
        {
            throw new NotImplementedException();
        }

        public DataTable Select()
        {
            string sql = @"SELECT *,(Case when gioitinh_nv = 1 then 'Nam' else N'Nữ' end) as sex,b.tenhv,c.tencd from nhanvien a join hocvi b on a.mahv=b.mahv join chucdanh c on a.macd=c.macd join quyen d on a.vaitro=d.maquyen";

            return ExecuteQuery(sql);
        }

        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        public bool Delete(string id)
        {
            string sql = @"DELETE FROM nhanvien WHERE ma_nv = '{0}'";
            sql = string.Format(sql,id);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert(object obj)
        {
            var o = (nhanvien)obj;
            string sql = @"set dateformat mdy; INSERT INTO nhanvien(ma_nv,ten_nv,gioitinh_nv,namsinh_nv,diachi_nv,sdt_nv,taikhoan,matkhau,macd,mahv,vaitro) VALUES('{0}',N'{1}','{2}','{3}',N'{4}','{5}','{6}','{7}','{8}','{9}',{10})";
            sql = string.Format(sql,o.ma_nv,o.ten_nv,o.gioitinh_nv,o.namsinh_nv,o.diachi_nv,o.sdt_nv,o.taikhoan,o.matkhau,o.macd,o.mahv,o.vaitro);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update(object obj)
        {
            var o = (nhanvien)obj;
            string sql = @"set dateformat mdy; UPDATE nhanvien SET ten_nv=N'{0}', gioitinh_nv='{1}', namsinh_nv='{2}', diachi_nv=N'{3}', sdt_nv='{4}',taikhoan='{5}',matkhau='{6}', mahv='{7}',macd='{8}',vaitro={9} WHERE ma_nv='{10}' ";
            sql = string.Format(sql, o.ten_nv, o.gioitinh_nv, o.namsinh_nv, o.diachi_nv, o.sdt_nv, o.matkhau,o.taikhoan,o.mahv,o.macd, o.vaitro ,o.ma_nv);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public DataTable Search(string name)
        {
            throw new NotImplementedException();
        }

        public DataTable Select_mahv()
        {
            string sql = @" SELECT mahv as id, tenhv as ten FROM HOCVI";
            return ExecuteQuery(sql);
        }

        public DataTable Select_macd()
        {
            string sql = @" SELECT macd as id, tencd as ten FROM CHUCDANH";
            return ExecuteQuery(sql);
        }
        public DataTable Select_quyen()
        {
            string sql = @" SELECT maquyen as id, tenquyen as ten FROM quyen";
            return ExecuteQuery(sql);
        }
        /// <summary>
        /// lay ...
        /// </summary>
        /// <returns></returns>
        public string Select_max()
        {
            string sql = @"SELECT max(substring(ma_nv,3,LEN(ma_nv)-2)) as id FROM nhanvien";
            var tb= ExecuteQuery(sql);
            
            return tb.Rows.Count>0? tb.Rows[0]["id"]+"":"0";
        }



        public bool deleteobj(object obj)
        {
            throw new NotImplementedException();
        }
    }
}
